package com.douyinkan.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

@Service
public class DatabaseService {
    
    private static final Logger logger = LoggerFactory.getLogger(DatabaseService.class);
    
    public void initializeDatabaseSchema(DataSource dataSource) {
        logger.info("=== Initializing database schema ===");
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            
            logger.info("Creating tables if they don't exist");
            
            // 创建内容表
            String createContentTable = """
                CREATE TABLE IF NOT EXISTS content (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    filename TEXT NOT NULL UNIQUE,
                    file_path TEXT NOT NULL UNIQUE,
                    content_type TEXT,
                    title TEXT,
                    description TEXT,
                    duration_seconds REAL,
                    width INTEGER,
                    height INTEGER,
                    file_size_bytes BIGINT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    thumbnail_path TEXT
                )
                """;
            logger.debug("Executing CREATE TABLE for content");
            statement.execute(createContentTable);
            logger.info("Content table ensured");
            
            // 验证表是否真的创建了
            try {
                statement.execute("PRAGMA table_info(content)");
                logger.debug("Content table verified to exist");
            } catch (SQLException e) {
                logger.warn("Could not verify content table existence", e);
            }
            
            // 创建标签表
            String createTagTable = """
                CREATE TABLE IF NOT EXISTS tag (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    name TEXT NOT NULL UNIQUE,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
                """;
            logger.debug("Executing CREATE TABLE for tag");
            statement.execute(createTagTable);
            logger.info("Tag table ensured");
            
            // 创建内容标签关联表
            String createContentTagTable = """
                CREATE TABLE IF NOT EXISTS content_tag (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    content_id INTEGER,
                    tag_id INTEGER,
                    FOREIGN KEY (content_id) REFERENCES content(id),
                    FOREIGN KEY (tag_id) REFERENCES tag(id)
                )
                """;
            logger.debug("Executing CREATE TABLE for content_tag");
            statement.execute(createContentTagTable);
            logger.info("Content-tag association table ensured");
            
            // 创建用户表
            String createUserTable = """
                CREATE TABLE IF NOT EXISTS user (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    username TEXT NOT NULL UNIQUE,
                    password TEXT NOT NULL,
                    email TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
                """;
            logger.debug("Executing CREATE TABLE for user");
            statement.execute(createUserTable);
            logger.info("User table ensured");
            
            logger.info("=== Database schema initialization completed successfully ===");
            
        } catch (SQLException e) {
            logger.error("Failed to initialize database schema", e);
            throw new RuntimeException("Failed to initialize database schema", e);
        }
    }
    
    // 保持旧的兼容方法，但使用默认数据源
    @Autowired
    private javax.sql.DataSource defaultDataSource;
    
    public void initializeDatabaseSchema() {
        logger.info("Initializing database schema with default data source");
        initializeDatabaseSchema(defaultDataSource);
    }
}